package com.anolesoft.yr.mbus.pre.sql;

public class PreOrderSQL {

    //新增报价单
    public static final String SAVE_PRE_ORDER = "insert into pre_order(objuid,fac_code,pre_inquiry_uid,quotation_code,quotation_date,quotation_person,phone,state,remark,c_user_uid,c_time,del_flag) values(#{objuid},#{facCode},#{preInquiryUid},#{quotationCode},#{quotationDate},#{quotationPerson},#{phone},#{state},#{remark},#{ccUserUid},#{ccTime},#{delFlag})";

    //查询报价单主键
    public static final String QUERY_PRE_ORDER_BY_INQUIRY_UID = "select a.objuid from pre_order a where a.del_flag=0 and a.pre_inquiry_uid=#{preInquiryUid}";

    //删除报价单
    public static final String DELETE_PRE_ORDER = "update pre_order a set a.del_flag=1 where a.objuid=#{objuid}";

    //查询报价单信息（回访询盘、生成、驳回）
    public static final String QUERY_PRE_ORDER_ALL = "select a.objuid,a.quotation_code,b.prod_production_uid,b.prod_package_uid,b.type_name,b.prod_name,c.part_model_uid,f.part_model,c.quantity,c.inquiry_price,c.offer_price,d.service_name as service_name2,d.service_value as service_value2,d.offer_price as offer_price2,e.service_name as service_name3,e.service_value as service_value3,e.offer_price as offer_price3 from pre_order a left join pre_order_prod b on b.pre_order_uid=a.objuid left join pre_order_part c on c.pre_order_prod_uid=b.objuid left join pre_order_service d on d.pre_order_part_uid=c.objuid left join pre_order_service_only e on e.pre_order_prod_uid=b.objuid left join part_model f on f.objuid=c.part_model_uid where a.del_flag=0 and a.state in(0,2) and a.pre_inquiry_uid=#{preInquiryUid} order by b.prod_name asc,f.part_model asc,d.service_name asc";

    //生成报价单
    public static final String UPDATE_PRE_ORDER_STATE1 = "update pre_order a set a.state=1 where a.objuid=#{objuid}";

    //查询报价单信息（审批）
    public static final String QUERY_PRE_ORDER_ALL2 = "select a.objuid,a.quotation_code,b.prod_production_uid,b.prod_package_uid,b.type_name,b.prod_name,c.part_model_uid,f.part_model,c.quantity,c.inquiry_price,c.offer_price,d.service_name as service_name2,d.service_value as service_value2,d.offer_price as offer_price2,e.service_name as service_name3,e.service_value as service_value3,e.offer_price as offer_price3 from pre_order a left join pre_order_prod b on b.pre_order_uid=a.objuid left join pre_order_part c on c.pre_order_prod_uid=b.objuid left join pre_order_service d on d.pre_order_part_uid=c.objuid left join pre_order_service_only e on e.pre_order_prod_uid=b.objuid left join part_model f on f.objuid=c.part_model_uid where a.del_flag=0 and a.state=1 and a.pre_inquiry_uid=#{preInquiryUid} order by b.prod_name asc,f.part_model asc,d.service_name asc";

    //报价单审批通过
    public static final String UPDATE_PRE_ORDER_STATE3 = "update pre_order a set a.state=3 where a.objuid=#{objuid}";

    //报价单审批驳回
    public static final String UPDATE_PRE_ORDER_STATE2 = "update pre_order a set a.state=2 where a.objuid=#{objuid}";

    //查询报价单信息（导出）
    public static final String QUERY_PRE_ORDER_ALL3 = "select a.objuid,a.quotation_code,b.prod_production_uid,b.prod_package_uid,b.type_name,b.prod_name,c.part_model_uid,f.part_model,c.quantity,c.inquiry_price,c.offer_price,d.service_name as service_name2,d.service_value as service_value2,d.offer_price as offer_price2,e.service_name as service_name3,e.service_value as service_value3,e.offer_price as offer_price3 from pre_order a left join pre_order_prod b on b.pre_order_uid=a.objuid left join pre_order_part c on c.pre_order_prod_uid=b.objuid left join pre_order_service d on d.pre_order_part_uid=c.objuid left join pre_order_service_only e on e.pre_order_prod_uid=b.objuid left join part_model f on f.objuid=c.part_model_uid where a.del_flag=0 and a.state=3 and a.pre_inquiry_uid=#{preInquiryUid} order by b.prod_name asc,f.part_model asc,d.service_name asc";

    //报价单导出
    public static final String UPDATE_PRE_ORDER_STATE4 = "update pre_order a set a.state=4 where a.objuid=#{objuid}";

    //报价单生效
    public static final String UPDATE_PRE_ORDER_STATE6 = "update pre_order a set a.state=6 where a.objuid=#{objuid}";

    //报价单关闭
    public static final String UPDATE_PRE_ORDER_STATE7 = "update pre_order a set a.state=7 where a.objuid=#{objuid}";
}
